﻿using System.Collections.Generic;
using System.Data.SQLite;
using System.Security.Claims;
using StudentManagementSystem.Models;
using var connection = new SQLiteConnection("Data Source=YourDb.db");

namespace StudentManagementSystem.Data
{
    public class ClassRepository
    {
        private readonly string _connectionString;

        public ClassRepository(string connectionString)
        {
            _connectionString = connectionString;
        }
        public void Add(Class @class)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "INSERT INTO Classes (Name, SchoolId) VALUES (@Name, @SchoolId); " +
                "SELECT last_insert_rowid();", connection);

            command.Parameters.AddWithValue("@Name", @class.Name);
            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);

            @class.Id = (int)(long)command.ExecuteScalar();
        }

        public IEnumerable<Class> GetBySchoolId(int schoolId)
        {
            var classes = new List<Class>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "SELECT * FROM Classes WHERE SchoolId = @SchoolId", connection);
            command.Parameters.AddWithValue("@SchoolId", schoolId);

            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                classes.Add(new Class
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    SchoolId = reader.GetInt32(2)
                });
            }
            return classes;
        }

        public Class GetById(int id)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Classes WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", id);

            using var reader = command.ExecuteReader();

            if (reader.Read())
            {
                return new Class
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    SchoolId = reader.GetInt32(2)
                };
            }
            return null;
        }

        public void Update(Class @class)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "UPDATE Classes SET Name = @Name, SchoolId = @SchoolId WHERE Id = @Id", connection);

            command.Parameters.AddWithValue("@Id", @class.Id);
            command.Parameters.AddWithValue("@Name", @class.Name);
            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);

            command.ExecuteNonQuery();
        }

        public void Delete(int id)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("DELETE FROM Classes WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", id);

            command.ExecuteNonQuery();
        }
    }
}